package com.alex.statistics.global;

import org.apache.poi.ss.usermodel.*;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.node.ArrayNode;
import com.fasterxml.jackson.databind.node.ObjectNode;

import java.io.*;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.text.DecimalFormat;
import java.util.*;

public class DynamicExcelToJsonConverter {

    // 使用Jackson的ObjectMapper，保持插入顺序
    private static final ObjectMapper objectMapper = new ObjectMapper();
    // 默认小数位数（可根据业务调整）
    private static final int DEFAULT_SCALE = 2;
    // 舍入模式（四舍五入）
    private static final RoundingMode ROUNDING_MODE = RoundingMode.HALF_UP;


    /**
     * 读取Excel文件路径并转换为JSON（对外接口，默认保留2位小数）
     */
    public static String convertExcelToJson(String filePath) throws IOException {
        return convertExcelToJson(filePath, DEFAULT_SCALE);
    }

    /**
     * 读取Excel文件路径并转换为JSON（支持自定义小数位数）
     */
    public static String convertExcelToJson(String filePath, int scale) throws IOException {
        try (InputStream inputStream = new FileInputStream(filePath)) {
            return convertExcelToJson(inputStream, scale);
        }
    }

    /**
     * 读取Excel输入流并转换为JSON（核心实现，支持自定义小数位数）
     */
    public static String convertExcelToJson(InputStream inputStream, int scale) throws IOException {
        ArrayNode jsonArray = objectMapper.createArrayNode();

        try (Workbook workbook = WorkbookFactory.create(inputStream)) {
            Sheet sheet = workbook.getSheetAt(0);
            Iterator<Row> rowIterator = sheet.iterator();

            if (!rowIterator.hasNext()) {
                return objectMapper.writerWithDefaultPrettyPrinter().writeValueAsString(jsonArray);
            }

            // 读取表头并保持顺序
            Row headerRow = rowIterator.next();
            List<String> headers = new ArrayList<>();
            Map<String, Integer> headerIndexMap = new LinkedHashMap<>();

            for (Cell cell : headerRow) {
                String headerName = getCellStringValue(cell, scale).trim();
                if (!headerName.isEmpty()) { // 跳过空表头
                    headers.add(headerName);
                    headerIndexMap.put(headerName, cell.getColumnIndex());
                }
            }

            if (headers.isEmpty()) {
                throw new IllegalArgumentException("Excel表头不能为空");
            }

            // 处理数据行
            while (rowIterator.hasNext()) {
                Row dataRow = rowIterator.next();
                ObjectNode jsonObject = objectMapper.createObjectNode();
                boolean hasData = false;

                // 按表头顺序添加字段
                for (String header : headers) {
                    int columnIndex = headerIndexMap.get(header);
                    Cell cell = dataRow.getCell(columnIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);

                    Object cellValue = getCellValueAsObject(cell, scale);
                    setJacksonNodeValue(jsonObject, header, cellValue);

                    if (!isCellValueEmpty(cellValue)) {
                        hasData = true;
                    }
                }

                if (hasData) {
                    jsonArray.add(jsonObject);
                }
            }
        }

        return objectMapper.writerWithDefaultPrettyPrinter().writeValueAsString(jsonArray);
    }

    /**
     * 向Jackson的ObjectNode设置值，处理数字格式化
     */
    private static void setJacksonNodeValue(ObjectNode node, String key, Object value) {
        if (value == null) {
            node.put(key, "");
            return;
        }

        if (value instanceof String) {
            node.put(key, (String) value);
        } else if (value instanceof Integer) {
            node.put(key, (Integer) value);
        } else if (value instanceof Long) {
            node.put(key, (Long) value);
        } else if (value instanceof BigDecimal) {
            // 对BigDecimal特殊处理：若为整数则转为Long，否则保留小数
            BigDecimal bd = (BigDecimal) value;
            if (bd.compareTo(bd.setScale(0, ROUNDING_MODE)) == 0) {
                node.put(key, bd.longValue());
            } else {
                node.put(key, bd.toPlainString());
            }
        } else if (value instanceof Boolean) {
            node.put(key, (Boolean) value);
        } else {
            node.put(key, value.toString());
        }
    }

    /**
     * 判断单元格值是否为空
     */
    private static boolean isCellValueEmpty(Object value) {
        if (value == null) {
            return true;
        }
        if (value instanceof String) {
            return ((String) value).trim().isEmpty();
        }
        if (value instanceof Number || value instanceof Boolean) {
            return false;
        }
        return true;
    }

    /**
     * 获取单元格值并转换为合适的Java对象（精确处理数值）
     */
    private static Object getCellValueAsObject(Cell cell, int scale) {
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue().trim();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    // 关键优化：用BigDecimal处理数值，限制小数位数
                    double numericValue = cell.getNumericCellValue();
                    return new BigDecimal(numericValue)
                            .setScale(scale, ROUNDING_MODE)  // 限制小数位数并四舍五入
                            .stripTrailingZeros(); // 去除末尾多余的0（如8.20 → 8.2）
                }
            case BOOLEAN:
                return cell.getBooleanCellValue();
            case FORMULA:
                return evaluateFormulaCell(cell, scale);
            default:
                return "";
        }
    }

    /**
     * 处理公式单元格（同样应用数值格式化）
     */
    private static Object evaluateFormulaCell(Cell cell, int scale) {
        Workbook workbook = cell.getSheet().getWorkbook();
        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
        CellValue cellValue = evaluator.evaluate(cell);

        switch (cellValue.getCellType()) {
            case STRING:
                return cellValue.getStringValue().trim();
            case NUMERIC:
                // 公式计算结果同样处理数值精度
                return new BigDecimal(cellValue.getNumberValue())
                        .setScale(scale, ROUNDING_MODE)
                        .stripTrailingZeros();
            case BOOLEAN:
                return cellValue.getBooleanValue();
            default:
                return "";
        }
    }

    /**
     * 获取单元格的字符串值（用于表头处理）
     */
    private static String getCellStringValue(Cell cell, int scale) {
        if (cell == null) {
            return "";
        }

        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    // 表头中的数值同样格式化
                    BigDecimal bd = new BigDecimal(cell.getNumericCellValue())
                            .setScale(scale, ROUNDING_MODE)
                            .stripTrailingZeros();
                    return bd.toPlainString();
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            default:
                return "";
        }
    }

    public static void main(String[] args) {
        try {
            // 测试：保留2位小数（默认）
            String filePath = "D:\\06.myproject\\statistics\\test.xls";
            String jsonFromPath = convertExcelToJson(filePath);
            System.out.println("默认保留2位小数：\n" + jsonFromPath);

            // 测试：保留4位小数（根据业务场景调整）
            String jsonWith4Decimals = convertExcelToJson(filePath, 4);
            System.out.println("保留4位小数：\n" + jsonWith4Decimals);

        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}